<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{
        var $_total = null;
        var $_pagination = null;
         
        
    
    public function __construct(){
    	parent::__construct();
            $mainframe = JFactory::getApplication();
             
                    // Get pagination request variables
                    $limit = $mainframe->getUserStateFromRequest('com_frclub.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
                    $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
             
                    // In case limit has been changed, adjust it
                    $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
             
                    $this->setState('limit', 5);
                    $this->setState('limitstart', $limitstart);
    }
    
    /**
	*Get data table PREF add in pulldown
	*/
    function getPREF(){
        $this->_db->setQuery("SELECT PREF_CODE, PREF_NAME FROM pref");
        return $this->_db->loadObjectList();
    }
    
    /**
	*Get data table CLUB add in pulldown
	*/
    function getCLUB(){
        $this->_db->setQuery("SELECT CLUB_CODE,CLUB_NAME FROM club WHERE CLUB_FLAG = 1");
        return $this->_db->loadObjectList();
    }
    
    /**
	*Get Total
	*/
    function getTotal($where = null){
        //Load the content if it doesn't already exist
        if($this->_total === null){
            $query = "SELECT COUNT(*) 
                            FROM ocba o
                            INNER JOIN town t
                            ON o.OCBA_TOWN_CODE = t.TOWN_CODE
                            INNER JOIN pref p
                            ON t.TOWN_PREF_CODE = p.PREF_CODE
                            INNER JOIN club c
                            ON o.OCBA_CLUB_CODE = c.CLUB_CODE $where";
          //  echo $query;
//            return;
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }
        return $this->_total;
    }
    
    /**
	*Get Pagination
	*/
    function getPagination(){
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
    }
    
    /**
	*Get data table OCBA add in list
	*/
    function getOCBA(){
        
        $keyword = JRequest::getVar('centerName');
        $pref = JRequest::getVar('pref');
        $club = JRequest::getVar('club');
        
        $where = array();
        $where[]="LEFT(OCBA_CODE,1) = 1";
        $where[]="OCBA_FLAG = 1";
        $where[]="OCBA_WEBF = 1";
        if($keyword != null){
            $where[] = " (OCBA_NAME LIKE '%$keyword%' OR OCBA_CODE LIKE '%$keyword%') ";
        }
          if($pref != 0){
            $where[] = "PREF_CODE = $pref ";
        }
        
        if($club != 0){
            $where[]= " CLUB_CODE =  $club ";
        }
        if($where){
            $where = ' WHERE '.implode(' AND ', $where);
        }else{
            $where = null;
        }
        
        $sql = "SELECT OCBA_CODE,OCBA_NAME,OCBA_KANA,OCBA_POST,OCBA_ADD1,CLUB_NAME,OCBA_TEL,OCBA_FAX,OCBA_HEAD,OCBA_EFLAG,OCBA_ADD2
                FROM ocba o
                INNER JOIN town t
                ON o.OCBA_TOWN_CODE = t.TOWN_CODE
                LEFT JOIN pref p
                ON t.TOWN_PREF_CODE = p.PREF_CODE
                INNER JOIN club c
                ON o.OCBA_CLUB_CODE = c.CLUB_CODE
                ";
        
        $this->_total = $this->getTotal($where);
        $sql .= $where;
        
        //Order by
        $sql .= " ORDER BY OCBA_CODE ASC";
        
//        return;
//        
        //Pagination
        $sql .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
        $this->_db->setQuery($sql);
        return $this->_db->loadObjectList();
    }
    
    /**
	*Load data detail  from ocba_code 
	*/
    function load($cid){
        
        $query="SELECT OCBA_TNAME,OCBA_REST,OCBA_TMAIL,OCBA_CMAIL,OCBA_PERI,OCBA_CNAME,OCBA_NAME,OCBA_ADD2,OCBA_IMG,OCBA_CODE,OCBA_DATE,OCBA_POST,OCBA_ADD1,OCBA_TEL,OCBA_LNAME,OCBA_LPOST,OCBA_HEAD,
            		OCBA_LADD1,OCBA_LADD2,OCBA_LTEL,OCBA_LFAX,OCBA_CONT,OCBA_SDATE,OCBA_SUPP,OCBA_PR,CLUB_NAME,TOWN_NAME,OCBA_FAX,OCBA_ENTR,OCBA_CLUB_DEPEND
            		FROM ocba INNER JOIN club ON ocba.OCBA_CLUB_CODE=club.CLUB_CODE
            					INNER JOIN town ON ocba.OCBA_TOWN_CODE=town.TOWN_CODE
                    WHERE  OCBA_CODE=($cid)";
        //echo $qr;
//        return;
        $this->_db->setQuery($query);
        $result = $this->_db->loadObject();
        //echo ($result->info_body);
         return $result;
    }
    
    /**
     * get info town from ocba_code
    **/
    function loadTown($cid){
        $qr = "SELECT OCBA_CODE,OCBA_NAME,OCBA_KANA,TOWN_CODE,TOWN_NAME,TOWN_KANA,TOWN_POST,TOWN_ADD1,TOWN_ADD2,TOWN_TEL,TOWN_FAX,TOWN_URL,TOWN_NAME 
                        FROM town LEFT JOIN ocba ON OCBA_TOWN_CODE = TOWN_CODE
                        WHERE TOWN_WEBF=1 AND OCBA_CODE = '$cid'";
       
       // load data
       // echo $qr;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }

      /**
     * get info town from ocba_code
    **/
    function loadClubInTown($town){
        $qr = "SELECT OCBA_CODE,OCBA_NAME FROM ocba
                        WHERE OCBA_WEBF=1 AND OCBA_TOWN_CODE = '$town' ";
       
       // load data
       // echo $qr;

       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
    }
    
    /**
     * get info OCBA from ocba_code
    **/
    function loadInfoOcba($cid){
        $qr = "SELECT OCBA_CODE,OCBA_NAME,OCBA_KANA,TOWN_NAME,OCBA_SHOR
                        FROM ocba INNER JOIN town ON ocba.OCBA_TOWN_CODE=town.TOWN_CODE
                        WHERE OCBA_WEBF=1 AND OCBA_CODE = '$cid'";
       
       // load data
       //echo $qr;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }
    /**
     * get info contact from ocba_code
    **/
    function loadContact($cid){
        $qr = "SELECT   OCBA_TNAME,OCBA_TMAIL,OCBA_CMAIL,OCBA_CNAME,OCBA_CNAME,OCBA_CMAIL,OCBA_LADD1,OCBA_LADD2,OCBA_DATE,OCBA_NAME,OCBA_URL,OCBA_MURL,OCBA_HEAD,OCBA_LNAME,OCBA_YNAME,OCBA_YMAIL,OCBA_ENAME,OCBA_EMAIL,OCBA_ONAME,OCBA_OMAIL 
                        FROM ocba INNER JOIN club ON ocba.OCBA_CLUB_CODE=club.CLUB_CODE
                        WHERE OCBA_WEBF=1 AND OCBA_CODE = '$cid'";
       
       // load data
       // echo $qr;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }
    
    
     /**
     * get info contact from ocba_code
    **/
    function loadActivity($cid){
        $qr = "SELECT   OCBA_CODE,ACTT_DATE,ACTT_NAME,OCBA_NAME,ACTT_RECR,ACTT_CODE,ACTT_FLAG,ACTT_REMARKS,OCBA_NAME,ACTT_PERIOD_START,ACTT_PERIOD_FINISH,OCBA_CODE,ACTT_NUMBER_OF_PARTICIPANTS,ACTT_RECOMMENDED_DEGREE
                    FROM actt INNER JOIN ocba ON actt.ACTT_OCBA_CODE=ocba.OCBA_CODE
                    WHERE ACTT_WEBF=1 AND OCBA_CODE=($cid)";
       
       // load data
       //echo $qr;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
    }
    
   
     /**
     * get info boathouse from ocba_code 
    **/
    function loadDock($cid){
        $qr = "SELECT DOWM_NAME,DOCK_TOYK,DOCK_SHWK,DOCK_WNAMEW,DOCK_SHHK,DOCK_CHAK,DOCK_PAKK,DOCK_TBDO_CODE_LIST,DOCK_CODE,DOCK_POST,DOCK_ADD1,DOCK_TEL,DOCK_FAX,DOCK_SDATE,DOCK_TDATE,DOCK_ADMN,DOCK_TERM,DOCM_NAME,DOCK_FTMF,DOCK_WNAME,DOCK_WNOTE,WATM_NAME,DOCK_IMGD,DOCK_IMGW,DOCK_IMGS,DOCK_CIAD,DOCK_CICI,DOCK_COAD,DOCK_COCI,DOCK_CNOT,DOCK_NOTE,DOCK_DATE
                        FROM dock
                        LEFT JOIN watm ON WATM_CODE = DOCK_WATM_CODE
                        LEFT JOIN docm ON DOCM_CODE = DOCK_DOCM_CODE
                        LEFT JOIN dowm ON DOWM_CODE=DOCK_DOWM_CODE
                        WHERE DOCK_WEBF = 1 AND DOCK_OCBA_CODE = '$cid'";
       // load data
       // echo $qr;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       //get data from table tbgy
       $qr2 = "SELECT DISP_CODE,DISP_NAME FROM disp";
       $this->_db->setQuery($qr2);
       $disp_list = $this->_db->loadObjectList();
                                  
       //group data: DOCK and DISP
       $disp_code_list = $result->DOCK_DISP_CODE_LIST;
       $disp_code_list = explode(",",$disp_code_list);
       $disabled = array();
       for($i = 0; $i < count($disp_code_list); $i++){
            for($j = 0; $j < count($disp_list); $j++){
                if($disp_code_list[$i] == $disp_list[$j]->DISP_CODE){
                    $disabled[] = $disp_list[$j]->DISP_NAME;
                }
            }
       }
       $result->disabled = $disabled;
        
       return $result;
    }

     /**
     * get info other_info from ocba_code chua lam
    **/
    function loadOtherInfo($cid){
        $qr = "SELECT *
                	FROM ocba
                    WHERE  OCBA_CODE=($cid)";
       
       // load data
       //echo $qr;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }
    
    
     /**
     * get info Coach from ocba_code chua lam
    **/
    function loadCoach($cid){
        $qr = "SELECT DISTINCT COCH_CD,COCH_NAME,COCH_WOKF,COCH_CLST,COCH_OCBA_CODE,OCBA_NAME,OCBA_CODE,COCH_CODE,COCH_LDATE
                       FROM coch LEFT JOIN ocba ON coch.COCH_OCBA_CODE=ocba.OCBA_CODE                                         
                                 LEFT JOIN coli ON coch.COCH_CODE = coli.COLI_COCH_CODE
                        ";
        $where = array();
        $where[] = "COCH_FLAG=1";
        $where[]=" OCBA_CODE=($cid) ";
        $where[]=" (coli.COLI_COCM_CODE = 1 OR coli.COLI_COCM_CODE = 2) ";
        $where[]=" COCH_LDATE > NOW() ";
        
       if($where){
            $where = ' WHERE '.implode(' AND ', $where);
        }else{
            $where = null;
        }
       // get total number record
       //$this->_totalCoch = $this->getTotalCoch($where);
    
         
       $qr .= $where;
       
       // order by
       $qr .= " ORDER BY COCH_DATE DESC, COCH_CODE DESC "; 
       //$qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
       // load data
       //echo $qr;
//        return;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
    }
    /**
     * Get total record in table coch
    **/
    function getTotalCoch($where = null){
        // Load the content if it doesn't already exist
        if ($this->_totalCoch === null) {
            
            $query = "SELECT COUNT(DISTINCT COCH_CODE)
                        FROM coch LEFT JOIN ocba ON coch.COCH_OCBA_CODE=ocba.OCBA_CODE                                         
                        LEFT JOIN coli ON coch.COCH_CODE = coli.COLI_COCH_CODE
                        $where";
            
            $this->_db->setQuery($query);
            $this->_totalCoch = $this->_db->loadResult();
        }
        
        return $this->_totalCoch;
    }
    
     /**
     * get info Count from ocba_code chua lam
    **/
    function loadCount($cid){
        $qr = "SELECT   OCBA_CODE,ACTT_NAME,ACTT_CODE,ACTT_FLAG,ACTT_REMARKS,OCBA_NAME,ACTT_PERIOD_START,ACTT_PERIOD_FINISH,OCBA_CODE,ACTT_NUMBER_OF_PARTICIPANTS,ACTT_RECOMMENDED_DEGREE,ACTT_DATE
                    FROM actt INNER JOIN ocba ON actt.ACTT_OCBA_CODE=ocba.OCBA_CODE
                    WHERE  OCBA_CODE=($cid)";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
    }
    
    function dates_inbetween($date1, $date2){

            $day = 60*60*24;
            
            $date1 = strtotime($date1);
            $date2 = strtotime($date2);
            
            $days_diff = round(($date2 - $date1)/$day); // Unix time difference devided by 1 day to get total days in between
            
            $dates_array = array();
            
            $dates_array[] = date('Y-m-d',$date1);
            
            for($x = 1; $x < $days_diff; $x++){
            $dates_array[] = date('Y-m-d',($date1+($day*$x)));
            }
            
            $dates_array[] = date('Y-m-d',$date2);
            
            return $dates_array;
}
 
    //load data qualification
    function loadcoli($cid)
    {
        $query="SELECT  COCM_NAME,COLI_UNIT,COLI_COCH_CODE,OCBA_NAME,COLI_TERM
                        FROM cocm INNER JOIN coli ON cocm.COCM_CODE=coli.COLI_COCM_CODE 
                     			INNER JOIN ocba ON ocba.OCBA_CODE=coli.COLI_OCBA_CODE
                               WHERE coli.COLI_COCH_CODE=($cid)";
        $this->_db->setQuery($query);
        $result = $this->_db->loadObjectList();
        
       // echo ($result->info_body);
        return $result;
        
    }
    
     /**
     * get actm
    **/
    function loadActm(){
        $qr = "SELECT * FROM actm";
       
       // load data
    
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
    }
    
     /**
     * get pacm
    **/


    function loadPacm(){
        $qr = "SELECT * FROM pacm";
       
       // load data
    
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
    }
    
       /**
     * get all TBDO - Thiết bị CSVC DOCK
    **/


    function loadTbdo(){
        $qr = "SELECT * FROM tbdo";
       
       // load data
    
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
    }
    
    
    /**
     * Load USCL for statistic
     */
    public function loadUscl($filter){
        
        $filter['period'] = 12;
        $filter['period_f'] = 1;
        $filter['month'] = '04';
                                
        //get the DB object
        $db = $this->_db;
        
        //get activity
        $activity = "(SELECT * FROM actm)";
        $db->setQuery($activity);
        $activity_list = $db->loadObjectList();
        
        //get category
        $category = "(SELECT * FROM pacm )";
        $db->setQuery($category);
        $category_list = $db->loadObjectList();
        
        //caculate start and end time
        $start_month = (int)$filter['month'];
        $start_year = $filter['year'];
        $end_month = "";
        $end_year = "";
        $end = "";
        
        //$data 
        $data = array();
        $last_update = 0;        

        for ($period = 1; $period <= $filter['period']; $period++){   
            $end_year = $start_year;
            $end_month = $start_month + $filter['period_f'] - 1;
            if($end_month > 12){
                $end_month -= 12;
                $end_year++;
            }
                        
            $start = $start_year."/".str_pad($start_month,2,'0',STR_PAD_LEFT);
            $end = $end_year."/".str_pad($end_month,2,'0',STR_PAD_LEFT);

            $data['head'][$period] = $filter['period_f'] == 1 ? $start_month."月" : $start_month." ~ ".$end_month."月";            
            
            $time = $start == $end ? " USCL_DATE = '$start' " : " USCL_DATE >= '$start' AND USCL_DATE <= '$end' ";
            foreach ($activity_list as $activity_key => $activity_row){
                //foreach PACM avaiable, get value
                foreach($category_list as $category_key => $category_row){   
                    $sql = 
                        "SELECT SUM(USCL_VAL) as val, MAX(USCL_UPDATE)as last_update FROM uscl 
                            JOIN $activity as activity ON USCL_ACTM_CODE = ACTM_CODE
                            JOIN $category as category ON USCL_PACM_CODE = PACM_CODE
                            WHERE 
                                USCL_OCBA_CODE IN ('$filter[ocba_code]')
                                AND $time
                                AND PACM_CODE = $category_row->PACM_CODE
                                AND ACTM_CODE = $activity_row->ACTM_CODE
                            ";
                    $db->setQuery($sql);
                    $result = $db->loadObject();
                    $val = empty($result->val) ? '-' : $result->val;
                    if(!empty($result->last_update) && (strtotime($result->last_update) > $last_update)){
                        $last_update = strtotime($result->last_update);
                    }
                    
                    //assign data value
                    $data['uscl_data'][$activity_row->ACTM_CODE][$category_row->PACM_CODE][$period] = $val;
                    if($category_row->PACM_NUMF == 1){
                        $data['sum_col'][$period] += $val;
                    } else {
                        $data['sum_col2'][$period] += $val;
                    }
                    $data['sum_row'][$activity_row->ACTM_CODE][$category_row->PACM_CODE] += $val; 
                }
            }
            
            $start_year = $end_year;
            $start_month = $end_month + 1;
                if($start_month > 12){
                    $start_month -= 12;
                    $start_year++;
                }
            
        }     
        
        //replace '0' => '-' for sum (row and col)
        foreach($data['sum_row'] as $k => $row){
            if($row == 0){
                $data['sum_row'][$k] = '-';
            }
        }      
        foreach($data['sum_col'] as $k => $row){
            if($row == 0){
                $data['sum_col'][$k] = '-';
            }
        }
        foreach($data['sum_col2'] as $k => $row){
            if($row == 0){
                $data['sum_col2'][$k] = '-';
            }
        }

        return $data;
    }
    function loadCenter($cid){
        $query="SELECT DISTINCT OCBA_CODE,DOCK_WNAMEW,OCBA_LNAME,OCBA_NAME,
        OCBA_KANA, OCBA_POST, OCBA_ADD1,OCBA_ADD2,OCBA_LADD2, 
        OCBA_TEL, OCBA_FAX, OCBA_CONT,OCBA_LPOST,OCBA_LADD1,OCBA_LTEL,
        OCBA_LFAX,POOL_OCBA_CODE,POOL_POHK,GYMT_OCBA_CODE,DOCK_OCBA_CODE,DOCK_DOCM_CODE,OCBA_PUTF,
        OCBA_LARF,OCBA_CANF,OCBA_YACF,OCBA_SCHF,OCBA_OUTF
                    FROM ocba
                    LEFT JOIN gymt ON OCBA_CODE = GYMT_OCBA_CODE
                    LEFT JOIN pool ON OCBA_CODE = POOL_OCBA_CODE
                    LEFT JOIN dock ON OCBA_CODE = DOCK_OCBA_CODE
                    WHERE OCBA_WEBF=1 AND OCBA_CODE=($cid)";

//        return;
        $this->_db->setQuery($query);
        $result = $this->_db->loadObject();
        //echo ($result->info_body);
         return $result;
    }
    
    
     /**
     * Get all list center
    **/
     function getAllClubPrint($list = null){

      $keyword = JRequest::getVar('centerName');
        $pref = JRequest::getVar('pref');
        $club = JRequest::getVar('club');
        
                $qr = "SELECT DISTINCT OCBA_CODE,OCBA_LNAME, OCBA_NAME,OCBA_KANA, OCBA_POST, OCBA_ADD1,OCBA_ADD2,OCBA_LADD2,OCBA_TEL, OCBA_FAX, OCBA_CONT,OCBA_LPOST,OCBA_LADD1,OCBA_LTEL,OCBA_LFAX,POOL_OCBA_CODE,GYMT_OCBA_CODE,DOCK_OCBA_CODE,OCBA_PUTF,OCBA_LARF,OCBA_CANF,OCBA_YACF,OCBA_SCHF,OCBA_OUTF,OCBA_EFLAG,CLUB_NAME
                    FROM ocba
                    LEFT JOIN gymt ON OCBA_CODE = GYMT_OCBA_CODE
                    LEFT JOIN pool ON OCBA_CODE = POOL_OCBA_CODE
                    LEFT JOIN dock ON OCBA_CODE = DOCK_OCBA_CODE
                    LEFT JOIN (
                    SELECT PREF_CODE, TOWN_CODE
                    FROM town
                    LEFT JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                    ) AS town_pref ON OCBA_TOWN_CODE = town_pref.TOWN_CODE
                    LEFT JOIN club ON OCBA_CLUB_CODE = CLUB_CODE ";
                   $where = array();
                   $where[]="LEFT(OCBA_CODE,1) = 1";
                   $where[]="OCBA_FLAG = 1";
                   $where[]="OCBA_WEBF = 1";
                   if($keyword != null){
                       $where[] = " (OCBA_NAME LIKE '%$keyword%' OR OCBA_CODE LIKE '%$keyword%') ";
                   }
                     if($pref != 0){
                       $where[] = "PREF_CODE = $pref ";
                   }
                   
                   if($club != 0){
                       $where[]= " CLUB_CODE =  $club ";
                   }
                   if($where){
                       $where = ' WHERE '.implode(' AND ', $where);
                   }else{
                       $where = null;
                   }
                     
                   $qr .= $where;
                   // order by
                   $qr .= " ORDER BY OCBA_CODE ASC"; 
                   //echo $qr;
                   $this->_db->setQuery($qr);
                   $result = $this->_db->loadObjectList();
        
       return $result;
  }
    /*function getAllClubPrint($list = null){

      $keyword = JRequest::getVar('centerName');
        $pref = JRequest::getVar('pref');
        $club = JRequest::getVar('club');
        
       $qr = "SELECT DISTINCT PREF_CODE,PREF_NAME
                    FROM pref INNER JOIN town ON TOWN_PREF_CODE=PREF_CODE
                              INNER JOIN ocba ON OCBA_TOWN_CODE=TOWN_CODE
              ";    
        $where = array();
        if($list['search_PREF_CODE'] != 0){
            $where[] = "PREF_CODE = '$list[search_PREF_CODE]'";
        }
        if($where){
            $where = ' WHERE '.implode(' AND ', $where);
        }else{
            $where = null;
        }
        $qr .= $where;
       // order by
       $qr .= " ORDER BY PREF_CODE DESC"; 
       
       // pagination
        //load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       //load data info
        foreach($result as $k => $row)
            {
                $a= $row->PREF_CODE;
                
                $qr = "SELECT DISTINCT OCBA_CODE,OCBA_LNAME, OCBA_NAME,OCBA_KANA, OCBA_POST, OCBA_ADD1,OCBA_ADD2,OCBA_LADD2,OCBA_TEL, OCBA_FAX, OCBA_CONT,OCBA_LPOST,OCBA_LADD1,OCBA_LTEL,OCBA_LFAX,POOL_OCBA_CODE,GYMT_OCBA_CODE,DOCK_OCBA_CODE,OCBA_PUTF,OCBA_LARF,OCBA_CANF,OCBA_YACF,OCBA_SCHF,OCBA_OUTF,OCBA_EFLAG,CLUB_NAME
                    FROM ocba
                    LEFT JOIN gymt ON OCBA_CODE = GYMT_OCBA_CODE
                    LEFT JOIN pool ON OCBA_CODE = POOL_OCBA_CODE
                    LEFT JOIN dock ON OCBA_CODE = DOCK_OCBA_CODE
                    LEFT JOIN (
                    SELECT PREF_CODE, TOWN_CODE
                    FROM town
                    LEFT JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                    ) AS town_pref ON OCBA_TOWN_CODE = town_pref.TOWN_CODE
                    LEFT JOIN club ON OCBA_CLUB_CODE = CLUB_CODE ";
                   $where = array();
                   $where[] = "PREF_CODE=$a";
                   $where[]="LEFT(OCBA_CODE,1) = 1";
                   $where[]="OCBA_FLAG = 1";
                   $where[]="OCBA_WEBF = 1";
                   if($keyword != null){
                       $where[] = " OCBA_NAME LIKE '%$keyword%' OR OCBA_CODE LIKE '%$keyword%' ";
                   }
                     if($pref != 0){
                       $where[] = "PREF_CODE = $pref ";
                   }
                   
                   if($club != 0){
                       $where[]= " CLUB_CODE =  $club ";
                   }
                   if($where){
                       $where = ' WHERE '.implode(' AND ', $where);
                   }else{
                       $where = null;
                   }
                     
                   $qr .= $where;
                   // order by
                   $qr .= " ORDER BY OCBA_CODE ASC"; 
                   //echo $qr;
                   $this->_db->setQuery($qr);
                   $data = $this->_db->loadObjectList();
                   if(empty($data)){
                      unset($result[$k]);
                      continue;
                   }
                   $row->data = $data;
                 
            }
        
       return $result;
	}*/
    
}
